package DAO;
//Data Access Object操作封装

import tools.Encription;
import tools.JDBCTools;
import vo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;

public class UserDAO {
    //按关键字查询，返回查询结果（对象），如果对象为null，表示没找到
    public static User get(String userName) throws Exception{
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_user where userName = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1,userName);
        ResultSet rs = pst.executeQuery();
        User currentUser = new User();
        if(rs.next()) {
            currentUser.setUserName(rs.getString("userName"));
            currentUser.setPassword(rs.getString("password"));
            currentUser.setName(rs.getString("name"));
            currentUser.setRole(rs.getString("role"));
        } else {// 若未找到用户记录，则将 currentUser 设置为 null
            currentUser = null;
        }
        JDBCTools.close(con); //关闭连接
        return currentUser;
    }
    //按任意条件组合（and）查询，返回查询结果（对象集合），如果不需要该条件，则相应属性值为null或者''
    public static ArrayList<User> query(User user) throws Exception{
        //查询结果（对象集合）
        ArrayList<User> users = new ArrayList<>();
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_user where username = '"+ user.getUserName() + "'";
        //name查询条件
        if(user.getName()!=null && !"".equals(user.getName())){
            sql = sql + " and name = '" + user.getName() + "'";
        }
        //role查询条件
        if(user.getRole()!=null && !"".equals(user.getRole())){
            sql = sql + " and role = '" + user.getRole() + "'";
        }
        PreparedStatement pst = con.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            User userResult = new User(rs.getString("userName"),
                    rs.getString("password"),rs.getString("name"),
                    rs.getString("role"));
            users.add(userResult);
        }
        JDBCTools.close(con);
        return users;
    }
    //插入的各字段存放在对象中，返回成功或失败
    public static boolean insert(User user) throws Exception{
        boolean success = false;
        //首先检查userName是否存在
        if(get(user.getUserName())==null){//不存在，可以插入
            Connection con = JDBCTools.getConnection();
            String sql = "insert into t_user values(?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1,user.getUserName());
            pst.setString(2,Encription.md5(user.getPassword()));
            pst.setString(3,user.getName());
            pst.setString(4,user.getRole());
            int i = pst.executeUpdate();
            if(i>0){
                success = true;
            }
            JDBCTools.close(con);
        }
        //存在，增加失败（key不能相同）
        //否则，增加
        return success;
    }
    //密码检验（查询操作）
    public static User check(String userName, String password) throws Exception{
        User currentUser = null;
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_user where userName = ? and password = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, userName);
        pst.setString(2, Encription.md5(password));
        ResultSet rs = pst.executeQuery();
        if(rs.next()) {
            currentUser = new User();
            currentUser.setUserName(rs.getString("userName"));
        }
        JDBCTools.close(con);
        return currentUser;
    }
    public static boolean update(String userName, String password) throws Exception{
        Scanner sc = new Scanner(System.in);
        User currentUser = check(userName, password);//查询该用户
        if(currentUser != null) {
            System.out.println("请输入新密码：");
            String new_pwd = sc.nextLine();
            Connection con = null;
            con = JDBCTools.getConnection();
            String sql = "UPDATE t_user SET password=? WHERE userName = ?";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1, Encription.md5(new_pwd));
            pst.setString(2, currentUser.getUserName());
            int flag = pst.executeUpdate(); // 执行更新语句
            if(flag > 0)
                return true; // 更新成功
            JDBCTools.close(con);
        }else{
            System.out.println("查无此人");
        }
        return false;
    }
    public static boolean delete(String userName, String password) throws Exception{
        //无法查询到该用户
        if(check(userName, password) == null){
            return false;
        }
        Connection con = JDBCTools.getConnection();
        String sql = "Delete from t_user where userName = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, userName);
        pst.executeUpdate();//执行删除语句
        JDBCTools.close(con);
        System.out.println("删除成功！");
        return true;
    }
}